package ru.alperez.gpsdub.transfer;

import java.util.ArrayList;
import java.util.List;

import ru.alperez.model.Creator;
import ru.alperez.model.GpsPoint;
import ru.alperez.model.Result;
import ru.alperez.model.ExtendedLocation;
import ru.alperez.util.Converter;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class DatabaseAdapter {
	private static final String TAG = "DatabaseAdapter";
	
	//Variable to hold the database instance
		private SQLiteDatabase db;
		// Database open/upgrade helper
		private SQLiteOpenHelper dbHelper;

		private int connectionCount = 0;

		/**
		 * Constructor
		 * @param context Context used by the database
		 */
		public DatabaseAdapter(Context context) {
			this.dbHelper = new DatabaseHelper(context);
		}

		//--------------------------------------------------------------------------------------

		/**
		 * Opens connection to the database for reading
		 * @throws SQLException if connection was not established
		 */
		public synchronized void openRead() throws SQLException {
			connectionCount++;
			db = dbHelper.getReadableDatabase();
		}

		/**
		 * Opens connection to the database for writing
		 * @throws SQLException if connection was not established
		 */
		public synchronized void openWrite() throws SQLException {
			connectionCount++;
			db = dbHelper.getWritableDatabase();
		}

		/**
		 * Close connection to the database
		 */
		public synchronized void close() {
			connectionCount--;
			if (connectionCount <= 0) {
				connectionCount = 0;
				db.close();
			}
		}

		//--------------------------------------------------------------------------------------

		/**
		 * Start transaction
		 */
		public void beginTransaction() {
			db.beginTransaction();
		}

		/**
		 * Commit transaction
		 */
		public void commitTransaction() {
			db.setTransactionSuccessful();
		}

		/**
		 * End transaction
		 */
		public void endTransaction() {
			db.endTransaction();
		}

		//--------------------------------------------------------------------------------------
		
		public long checkPointExists(long time) {
			long ret = -1;
			String columns[] = {"name", "dateTime", "(SELECT COUNT(*) FROM Point AS t2 WHERE t2.dateTime <= t1.dateTime)"};
			String filter = String.format("dateTime = %d", time);
			Cursor cursor = db.query("Point as t1", columns, filter, null, null, null, null);
			if (cursor.moveToFirst()) {
				ret = cursor.getLong(2);
			}
			return ret;
		}
		
		public Result insertPoint(GpsPoint point) {
			if (point == null || point.getLocation() == null) {
				return new Result(false, Result.ErrorCode.ERROR_BAD_ARGUMENT, "Argubent is NULL");
			}
			ExtendedLocation location = point.getLocation();
			if (checkPointExists(location.getTime()) > 0) {
				return new Result(false, Result.ErrorCode.ERROR_POINT_EXISTS, "Point already exists");
			}
			
			ContentValues values = new ContentValues();
			values.put("name", point.getName());
			values.put("description", point.getDescription());
			values.put("localDate", point.getLocalDate());
			values.put("localTime", point.getLocalTime());
			values.put("accuracy", location.getAccuracy());
			values.put("altitude", location.getAltitude());
			values.put("bearing", location.getBearing());
			values.put("latitude", location.getLatitude());
			values.put("longitude", location.getLongitude());
			values.put("provider", location.getProvider());
			values.put("speed", location.getSpeed());
			values.put("dateTime", location.getTime());
			values.put("nsat", point.getNsat());
			values.put("creatorId", insertCreator(point.getCreator()));
			long result = db.insert("Point", null, values);
			
			if (result > 0) {
				return new Result(true, Result.ErrorCode.ERROR_NO_ERROR, null);
			} else {
				return new Result(false, Result.ErrorCode.ERROR_POINT_INSERT, "Error insert point in DB");
			}
		}
		
		public long checkCreatorExists(Creator creator) {
			if (creator == null) return -1;
			long ret = -1;
			String filter = String.format("name = '%s' AND imei = '%s'", creator.getName(), creator.getImei());
			Cursor cursor = db.query("Creator", new String[]{"id"}, filter, null, null, null, null);
			if (cursor.moveToFirst()) {
				ret = cursor.getLong(0);
			}
			cursor.close();
			return ret;
		}
		
		public long insertCreator(Creator creator) {
			Log.d(TAG, "<insertCreator()> - start");
			if (creator == null) return -1;
			long id = checkCreatorExists(creator);
			if (id > 0) {
				Log.d(TAG, "<insertCreator()> - already exists - "+id);
				return id;
			}
			ContentValues values = new ContentValues();
			values.put("name", creator.getName());
			values.put("description", creator.getDescription());
			values.put("device", creator.getDevice());
			values.put("model", creator.getModel());
			values.put("imei", creator.getImei());
			long rez = db.insert("Creator", null, values);
			Log.d(TAG, "<insertCreator()> - result = "+rez);
			return rez;
		}
		/*
		public GpsPoint selectPoint(double lat, double lon, double alt, long time) {
			GpsPoint result = null;
			
			String[] columns = { "name", "description", "localDate", "localTime", "nsat", "accuracy", "altitude",
					"bearing", "latitude", "longitude", "provider", "speed", "dateTime", "creatorId", "(SELECT COUNT(*) FROM Point AS t2 WHERE t2.dateTime <= t1.dateTime)"};
			String filter = String.format("latitude = '"+Converter.doubleDegrees2String(lat)+"' AND longitude = '"+Converter.doubleDegrees2String(lon)+"' AND altitude = '"+Converter.floatAltitude2String((float)alt)+"' AND dateTime = %d", time);
			Cursor cursor = db.query("Point as t1", columns, filter, null, null, null, "dateTime");
			if (cursor.moveToFirst()) { //At least one row present		
				do {
					result = new GpsPoint();
					result.setName(cursor.getString(0));
					result.setDescription(cursor.getString(1));
					result.setLocalDate(cursor.getInt(2));
					result.setLocalTime(cursor.getInt(3));
					result.setNsat(cursor.getInt(4));
					Location location = new Location(cursor.getString(10));
					location.setAccuracy(cursor.getFloat(5));
					location.setAltitude(Float.parseFloat(cursor.getString(6)));
					location.setBearing(cursor.getFloat(7));
					location.setLatitude(Double.parseDouble(cursor.getString(8)));
					location.setLongitude(Double.parseDouble(cursor.getString(9)));
					location.setSpeed(cursor.getFloat(11));
					location.setTime(cursor.getLong(12));
					result.setLocation(location);
					result.setCreator(selectCreator(cursor.getLong(13)));
					result.setOrder(cursor.getInt(14));
				} while (cursor.moveToNext());
			}
			cursor.close();
			return result;
		}*/
		
		public Creator selectCreator(long id) {
			Creator result = null;
			String[] columns = {"name", "description", "imei", "device", "model"};
			Cursor cursor = db.query("Creator", columns, String.format("id = %d", id), null, null, null, null);
			if (cursor.moveToFirst()) { //At least one row present		
				do {
					result = new Creator();
					result.setName(cursor.getString(0));
					result.setDescription(cursor.getString(1));
					result.setImei(cursor.getString(2));
					result.setDevice(cursor.getString(3));
					result.setModel(cursor.getString(4));
				} while (cursor.moveToNext());
			}
			cursor.close();
			return result;
		}
		
		public List<GpsPoint> selectPoints() {
			List<GpsPoint> ret = new ArrayList<GpsPoint>();
			String[] columns = { "name", "description", "localDate", "localTime", "nsat", "accuracy", "altitude",
					"bearing", "latitude", "longitude", "provider", "speed", "dateTime", "creatorId"};
			Cursor cursor = db.query("Point", columns, null, null, null, null, "dateTime");
			if (cursor.moveToFirst()) { //At least one row present		
				int order = 1;
				do {
					GpsPoint point = new GpsPoint();
					point.setName(cursor.getString(0));
					point.setDescription(cursor.getString(1));
					point.setLocalDate(cursor.getInt(2));
					point.setLocalTime(cursor.getInt(3));
					point.setNsat(cursor.getInt(4));
					ExtendedLocation location = new ExtendedLocation(cursor.getString(10));
					location.setAccuracy(cursor.getFloat(5));
					location.setAltitude(cursor.getDouble(6));
					location.setBearing(cursor.getFloat(7));
					location.setLatitude(cursor.getDouble(8));
					location.setLongitude(cursor.getDouble(9));
					location.setSpeed(cursor.getFloat(11));
					location.setTime(cursor.getLong(12));
					point.setLocation(location);
					point.setCreator(selectCreator(cursor.getLong(13)));
					point.setOrder(order ++);
					ret.add(point);
				} while (cursor.moveToNext());
			}
			cursor.close();
			Log.d(TAG, "<selectPoints()> - points selected - "+ret.size());
			return ret;
		}
		
		public int deletePoint(GpsPoint point) {
			if (point == null || point.getLocation() == null) return 0;
			ExtendedLocation location = point.getLocation();
			String filter = String.format("latitude = '"+Converter.doubleDegrees2String(location.getLatitude())+"' AND longitude = '"+Converter.doubleDegrees2String(location.getLongitude())+"' AND altitude = '"+Converter.floatAltitude2String((float)location.getAltitude())+"' AND time = %d", location.getTime()); 
			return db.delete("Point", filter, null);
		}
}
